from django.contrib.auth.models import User
from django.template.loader import get_template
from config.conf import *

# Create your models here.

def getAllCategory():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select cid, name from category"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows
def getCategoryByID(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select * from category where cid = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]
    
def getAllBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name, b.feature, b.special from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows
def getBookEditByID(nid):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.title, c.weight, b.summary, b.detail, c.name from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and b.nid=%s" %(nid)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]
    
def getBookFromName(bName):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name,b.feature, b.special from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and e.title='%s'" % (bName)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getBookFromCategory(cName):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name,b.feature, b.special from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and c.name='%s'" % (cName)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows
    
def getBookOfCategory(cid):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title, e.created, c.name, b.feature, b.special from entity e, category c, book b where e.nid = b.nid and b.cid = c.cid and c.cid = %s" % (cid)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows
def editBook(nid, title, weight, summary, detail, category):
    db = initDataBase()
    cursor = db.cursor()
    sql = "update book set summary = '%s', detail = '%s', cid = (select cid from category as c where c.name = '%s') where nid = '%s' " % (summary, detail, category, nid)
    sql1 = "update entity set title = '%s', weight = '%s' where nid = '%s'" % (title, weight, nid)
    cursor.execute(sql)
    cursor.execute(sql1)
    db.commit()
    db.close()
    return
    
def deleteBook(nid):
    db = initDataBase()
    cursor = db.cursor()
    sql = "delete from book where nid = '%s'" % (nid)
    cursor.execute(sql)
    db.commit()
    db.close()
    return
    
def setSpecialBook(nid):
    db = initDataBase()
    cursor = db.cursor()
    sql = "update book set special = (1-special) where nid = '%s'" % (nid)
    cursor.execute(sql)
    db.commit()
    db.close()
    return

def setFeatureBook(nid):
    db = initDataBase()
    cursor = db.cursor()
    sql = "update book set feature = (1-feature) where nid = '%s'" % (nid)
    cursor.execute(sql)
    db.commit()
    db.close()
    return
    
def getCategoryID():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT * FROM category"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    row = rows[0]
    return row[0]


def addNewCategory(name, weight, status):
    db = initDataBase()
    cursor = db.cursor()
    sql = """INSERT INTO category( name, weight, status)
        VALUES ('%s', '%s', '%s')""" % (name, weight, status)
    cursor.execute(sql)
    db.commit()
    db.close()
    return

def updateCategory(cid, name, weight, status):
    db = initDataBase()
    cursor = db.cursor()
    sql = "update category set name = '%s', weight = '%s', status = '%s' where cid = '%s' " % (name, weight, status, cid)
    cursor.execute(sql)
    db.commit()
    db.close()
    return
def deleteCategory(cid):
	db = initDataBase()
	cursor = db.cursor()
	sql = "select b.nid from book as b where b.cid = %s"%(cid)
	cursor.execute(sql)
	rows = cursor.fetchall()
	for row in rows:
		sql = "delete from entity where nid = %s" % (row[0])
		cursor.execute(sql)
		db.commit()
	db.commit()
	sql = "delete from category where cid = '%s'" % (cid)
	cursor.execute(sql)
	db.commit()
	db.close()
	return
    
def getAllUser():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select * from auth_user"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows

def getUserByID(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select * from auth_user where id = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]
    
def getUserInfoByID(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select * from user_info where id = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]
    
def deleteUser(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "delete from auth_user where id = '%s'" % (id)
    cursor.execute(sql)
    db.commit()
    sql1 = "delete from user_info where id = '%s'" % (id)
    cursor.execute(sql1)
    db.commit()
    db.close()
    return
def updateUser(id, password, email, company, phone, address):
    user = User.objects.get(id=id)
    user.email = email
    if user.password != password:
       user.set_password(password)
    user.save()
    db = initDataBase()
    cursor = db.cursor()
    sql = "update user_info set company = '%s', phone = '%s', address = '%s' where id = '%s'" % (company, phone, address, id)
    cursor.execute(sql)
    db.commit()
    db.close()
    return 
def getAllStatic():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select e.nid, e.title,e.created from entity as e, static as s WHERE e.nid = s.nid"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows    
    
def getAllSetting():
    db = initDataBase()
    cursor = db.cursor()
    sql = "select configuration.id,configuration.key,configuration.value from configuration"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows        
    
def getDetailStatic(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select detail,value from static WHERE nid = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]
    
def getDetailSetting(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "select c.key,c.value from configuration as c WHERE id = %s" % (id)
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    return rows[0]    
    
def submitEditStatic(id, detail):
    db = initDataBase()
    cursor = db.cursor()
    sql = "UPDATE static SET detail = '%s' WHERE nid = %s" % (detail, id)
    cursor.execute(sql)
    db.commit()
    db.close()
    return
    
def submitEditSetting(id, detail):
    db = initDataBase()
    cursor = db.cursor()
    sql = "UPDATE configuration as c SET c.value = '%s' WHERE id = %s" % (detail, id)
    cursor.execute(sql)
    db.commit()
    db.close()
    return    
    
def getBookName():
    listName = ''
    db = initDataBase()
    cursor = db.cursor()
    sql = "select title from entity Where type ='book'"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    for row in rows:
        listName += '"%s",' % (row[0])
    return listName
    
def getCatName():
    listName = ''
    db = initDataBase()
    cursor = db.cursor()
    sql = "select name from category"
    cursor.execute(sql)
    rows = cursor.fetchall()
    db.close()
    for row in rows:
        listName += '"%s",' % (row[0])
    return listName
    
def addABook(title, weight, summary, detail, category, thumbnail):
    db = initDataBase()
    cursor = db.cursor()
    sql = "INSERT INTO entity(title,type,weight) VALUES('%s','book',%s)" % (title, weight)
    cursor.execute(sql)
    db.commit()
    sql = "SELECT MAX(nid) from entity"
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    eid = row[0]
    sql = "SELECT cid from category WHERE name = '%s'" % (category)
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    cid = row[0]
    thumbnail = 'add_new_book.jpg'
    sql = "INSERT INTO book(nid,author,summary,detail,thumbnail,cid) VALUES('%s','GROUP 4','%s','%s','%s','%s')" % (eid, summary, detail, thumbnail, cid)
    cursor.execute(sql)
    db.commit()
    db.close()

def addRelatedBook(id):
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT MAX(nid) from entity"
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    bid = row[0]
    sql = "INSERT INTO related_book  VALUES('%s','%s');" % (bid, id)
    cursor.execute(sql)
    db.commit()
    sql = "INSERT INTO related_book  VALUES('%s','%s');" % (id, bid)
    cursor.execute(sql)
    db.commit()
    db.close()
    
def getNumBook():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT count(*) from entity WHERE type='book'"
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    return row[0]
    
def getNumUser():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT count(*) from auth_user"
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    return row[0]

def getNumLike():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT sum(nlike) from book"
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    return row[0]  

def getNumSold():
    db = initDataBase()
    cursor = db.cursor()
    sql = "SELECT sum(sold) from book"
    cursor.execute(sql)
    rows = cursor.fetchall()
    row = rows[0]
    return row[0]    